package ma.android.oook;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

	
public class OookDbAdapter {

	    private static final String TAG = "OookDbAdapter";
	    private static DatabaseHelper mDbHelper;
	    private static SQLiteDatabase mDb;
	    
	    private static final String DATABASE_NAME = "OookDb";
	    private static final String DATABASE_TABLE_ITEMS= "oook_item";
	    private static final String DATABASE_TABLE_TAGS= "oook_tag";
	    private static final String DATABASE_TABLE_RELATION = "oook_relation";
	    private static final int DATABASE_VERSION = 1;

	    private final Context mCtx;

	    private static class DatabaseHelper extends SQLiteOpenHelper {

	        DatabaseHelper(Context context) {
	            super(context, DATABASE_NAME, null, DATABASE_VERSION);
	        }

	        @Override
	        public void onCreate(SQLiteDatabase db) {

	        	db.execSQL("create table if not exists oook_item (" +
	    				" id integer primary key autoincrement, " +
	    				" type text, " +
	    				" description text, " +
	    				" amount integer, " +
	    				" contact_name text," +
	    				" contact_id text," +
	    				" deadline_date integer," +
	    				" latitude real null," +
	    				" longitude real null, " +
	    				" loan boolean" +
	    		"); ");
	        	
	        	db.execSQL("create table if not exists oook_tag (" +
	        			" id integer primary key autoincrement, " +
	        			" description text" +
	        			
	        	"); ");
	        	
	        	db.execSQL("create table if not exists oook_relation (" +
	        			" item_id integer not null references oook_item (id), " +
	        			" tag_id integer not null references oook_tag(id)," +
	        			" primary key (item_id,tag_id) " +
	        	"); ");
	        }

	        @Override
	        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
	                    + newVersion + ", which will destroy all old data");
	            onCreate(db);
	        }
	    }

	    
	    public OookDbAdapter(Context ctx) {
	        mCtx = ctx;
	        //mCtx.deleteDatabase(DATABASE_NAME);
	    }

	    /**
	     * Open the notes database. If it cannot be opened, try to create a new
	     * instance of the database. If it cannot be created, throw an exception to
	     * signal the failure
	     * 
	     * @return this (self reference, allowing this to be chained in an
	     *         initialization call)
	     * @throws SQLException if the database could be neither opened or created
	     */
	    public OookDbAdapter open() throws SQLException {
	        mDbHelper = new DatabaseHelper(mCtx);
	        mDb = mDbHelper.getWritableDatabase();
	        return this;
	    }
	    
	    public void close() {
	        mDbHelper.close();
	    }
	    public void saveItem(OookItem item) {
	        ContentValues cv = new ContentValues();
			cv.put("type", item.getType());
			cv.put("description", item.getDescription());
			cv.put("amount", item.getAmount());
			cv.put("contact_name", item.getContactName());
			cv.put("contact_id", item.getContactId());
			cv.put("deadline_date", item.getDeadlineDate());
			cv.put("latitude", item.getLatitude());
			cv.put("longitude", item.getLongitude());
			cv.put("loan", item.isLoan());
			long newId = mDb.insert(DATABASE_TABLE_ITEMS, null, cv);
			
			for (int tag : item.getTagCode()) 
			{
				ContentValues cvf = new ContentValues();
				cvf.put("item_id", newId);
				cvf.put("tag_id", tag);
				mDb.insert(DATABASE_TABLE_RELATION, null,cvf);
			}
	    }
	    public void  saveTag(String description)
	    {
	    	ContentValues cv = new ContentValues();
			cv.put("description", description);
			mDb.insert(DATABASE_TABLE_TAGS, null, cv);
	    }
	    public void deleteTag(int tagId)
	    {
	    	mDb.delete(DATABASE_TABLE_ITEMS, "id = ?", new String[]{String.valueOf(tagId)});
	    }
	    
	    public void createTags()
	    {
	    	ContentValues cv = new ContentValues();
	    	cv.put("description", "Friends");
	    	mDb.insert(DATABASE_TABLE_TAGS, null, cv);
	    	cv.put("description", "Family");
	    	mDb.insert(DATABASE_TABLE_TAGS, null, cv);
	    	cv.put("description", "Work");
	    	mDb.insert(DATABASE_TABLE_TAGS, null, cv);
	    	cv.put("description", "Others");
	    	mDb.insert(DATABASE_TABLE_TAGS, null, cv);
	    }
	    
	    public Cursor findTags()
	    {
	    	return mDb.query(DATABASE_TABLE_TAGS, new String[]{"id", "id as _id", "description"}, null, null, null, null, null);
	    }
	    public Cursor findAllLoans()
	    {
	    	return mDb.query(DATABASE_TABLE_ITEMS, new String[]{"id","id as _id","type","description","amount", 
	    		"contact_id","contact_name","deadline_date","latitude","longitude","loan" }," loan = 1 ", null, null, null, "deadline_date desc");
	    }
	    public Cursor findAllBorrowed()
	    {
	    	return mDb.query(DATABASE_TABLE_ITEMS, new String[]{"id","id as _id","type","description","amount", 
	    		"contact_id","contact_name","deadline_date","latitude","longitude","loan" }," loan = 0 ", null, null, null, "deadline_date desc");
	    }
	    public Cursor findAll()
	    {
	    	return mDb.query(DATABASE_TABLE_ITEMS, new String[]{"id","id as _id","type","description","amount", 
	    		"contact_id","contact_name","deadline_date","latitude","longitude","loan"  },null, null, null, null, null);
	    }
	    public Cursor findLoan(int loadId)
	    {
	    	return mDb.query(DATABASE_TABLE_ITEMS, new String[]{"id","id as _id","type","description","amount", 
				"contact_id", "contact_name","deadline_date","latitude","longitude","loan" }," loan = 1 AND id = ?", new String[]{String.valueOf(loadId)}, null, null, null);
	    }
	    public Cursor findItem(int loadId)
	    {
	    	return mDb.query(DATABASE_TABLE_ITEMS, new String[]{"id","id as _id","type","description","amount", 
				"contact_id", "contact_name","deadline_date","latitude","longitude","loan" },"id = ?", new String[]{String.valueOf(loadId)}, null, null, null);
	    }
	    public Cursor findBorrow(int borrowId)
	    {
	    	return mDb.query(DATABASE_TABLE_ITEMS, new String[]{"id","id as _id","type","description","amount", 
	    			"contact_id", "contact_name","deadline_date","latitude","longitude","loan" }," loan = 0 AND id = ?", new String[]{String.valueOf(borrowId)}, null, null, null);
	    }
	    public Cursor findTag (String tagDescription)
	    {
	    	return mDb.query(DATABASE_TABLE_TAGS, new String[]{"id"}," description = ?",  new String[]{tagDescription}, null, null, null);
	    }
	    public Cursor totalLoans()
	    {
	    	return mDb.query(DATABASE_TABLE_ITEMS, new String[]{"sum(amount)"}," loan = 1 AND type='Money'", null, null, null, null);
	    }
	    public Cursor totalDebts()
	    {
	    	return mDb.query(DATABASE_TABLE_ITEMS, new String[]{"sum(amount)"}," loan = 0 AND type='Money'", null, null, null, null);
	    }
	    public Cursor findRelationByItemCode(int itemCode)
	    {
	    	return mDb.query(DATABASE_TABLE_RELATION, new String[]{"tag_id"},"item_id = ?", new String[]{String.valueOf(itemCode)}, null, null, null);
	    }
	    public void updateTags(int itemId, int [] tags)
	    {
	    	ContentValues cv;
	    	mDb.delete(DATABASE_TABLE_RELATION, "item_id = ?", new String[]{String.valueOf(itemId)});
	    	for (int i = 0; i < tags.length; i ++)
	    	{
	    		cv = new ContentValues();
				cv.put("item_id", itemId);
				cv.put("tag_id", tags[i]);
				mDb.insert(DATABASE_TABLE_RELATION, null, cv);
	    	}
	    }
	    public void updateItem(OookItem item)
	    {
	    	ContentValues cv = new ContentValues();
			cv.put("type", item.getType());
			cv.put("description", item.getDescription());
			cv.put("amount", item.getAmount());
			cv.put("contact_name", item.getContactName());
			cv.put("contact_id", item.getContactId());
			cv.put("deadline_date", item.getDeadlineDate());
			cv.put("latitude", item.getLatitude());
			cv.put("longitude", item.getLongitude());
			cv.put("loan", item.isLoan());
	    	mDb.update(DATABASE_TABLE_ITEMS,cv, "id = ?", new String[]{String.valueOf(item.getId())});
	    	updateTags(item.getId(), item.getTagCode());
	    }
}